Associate Data Practitioner Practice Exam — Associate Data Practitioner

This question evaluates core BigQuery ML syntax proficiency and model selection knowledge for classification tasks, a high-priority domain for the Associate Data Practitioner certification. The scenario specifies a binary classification use case (predicting yes/no customer churn) that requires a logistic regression model trained on the customer_data table with the churned column as the target label. The suggested answer B fully aligns with all requirements: it follows valid BigQuery ML CREATE MODEL syntax, specifies the correct model type for logistic regression, explicitly identifies the churned column as the target label, and uses the customer_data table as the training data source, so it will successfully generate the required churn prediction model. Option Analysis: A. Incorrect. This option uses model_type='LINEAR_REG' which is designed for regression tasks predicting continuous numeric values, not binary classification for categorical churn labels. Linear regression cannot produce valid probability or class outputs for churn prediction, so this choice is invalid. B. Correct. This option adheres to all BigQuery ML requirements for the specified task: it sets model_type='logistic_reg' to implement the required logistic regression model, defines input_label_cols=['churned'] to set the correct target label, and queries the customer_data table as the training data source, fully meeting the scenario requirements. C. Incorrect. This option omits the required input_label_cols parameter in the OPTIONS clause, so BigQuery ML cannot recognize the churned column as the target label. This will either result in training failure or the model incorrectly using churned as an input feature rather than the prediction target. D. Incorrect. This option uses model_type='RANDOM_FOREST_CLASSIFIER' which is a valid classification model but not the logistic regression model explicitly required in the question. It does not meet the specified model type requirement, so it is incorrect. Key Concepts: 1. BigQuery ML Model Type Mapping: Each BQML model type is designed for a specific task type. LOGISTIC_REG is intended for binary and multi-class classification tasks with categorical target labels, while LINEAR_REG is built for regression tasks predicting continuous numeric outputs. Matching the model type to the task is a foundational requirement for valid model training. 2. BQML CREATE MODEL Core Parameters: The OPTIONS clause of the CREATE MODEL statement requires two critical parameters for most supervised learning use cases: model_type to define the model architecture, and input_label_cols to specify the target column the model is trained to predict. Misconfiguring these parameters leads to invalid or non-functional models. 3. Binary Classification Use Case Framing: Customer churn prediction is a standard binary classification use case, where the target variable has only two possible categorical values (churned or not churned). This requires classification model types rather than regression or unsupervised model types to produce valid outputs. References: BigQuery ML Logistic Regression Create Syntax, BigQuery ML CREATE MODEL Overview, https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-create

The scenario requires calculating a weekly moving average of total daily sales per retail location to identify store-specific trends, which relies on two core SQL execution steps aligned with All Associate Data Practitioner certification competencies. First, raw sales records must be aggregated to get total daily sales per location, as the source data tracks individual sales transactions. Second, a rolling 7-day average must be calculated independently for each location to avoid mixing data across different stores. The correct query (Option C) executes both steps properly: it first groups sales data by location and calendar date to compute daily total sales per store, then applies a window function that partitions the dataset by location to isolate each store’s time series, orders the partitioned data by sale date to maintain chronological sequence, and defines a window frame covering the current day plus the preceding 6 days (7 total days for a full week) to compute the average daily sales. This directly delivers the store-specific weekly moving averages required for trend analysis, which is a common use case tested for retail and operational analytics in the certification. Option Analysis: A. This option is incorrect. It omits the PARTITION BY location clause in the window function, so it calculates a single global moving average across all retail locations instead of location-specific metrics. This fails to deliver the store-level trend data required by the scenario, as it averages performance across all locations rather than isolating individual store results. B. This option is incorrect. It either skips the initial aggregation of sales to daily totals per location, leading to averaging of individual transaction values instead of daily sales totals, or uses an incorrect window frame of 7 PRECEDING rows which creates an 8-day average instead of the required 7-day weekly average. Both mistakes produce invalid metrics that do not match the requirement. C. This option is correct. It properly aggregates daily total sales per location first to align with the source data structure, uses a window function partitioned by location to generate store-specific calculations, orders the partitioned data by sale date to ensure chronological accuracy, and uses a window frame of 6 PRECEDING rows AND CURRENT ROW to create a 7-day rolling average that exactly matches the weekly moving average requirement for trend identification. D. This option is incorrect. It uses a GROUP BY week and location clause to calculate static weekly average sales instead of a rolling moving average. This produces one average value per store per full calendar week, rather than a daily updated moving average that can capture granular day-over-day trend changes required for proactive store performance monitoring. Key Concepts: 1. Window Function Time Series Aggregation: This core certification concept covers the use of SQL window functions to compute rolling metrics such as moving averages, running totals, and period-over-period changes, which are foundational for trend analysis across common use cases including retail sales, customer behavior, and operational performance. 2. Window Partitioning: The PARTITION BY clause in window functions splits datasets into independent groups so calculations are executed separately for each group. This is a critical skill tested in the certification for building group-specific business metrics, such as store-specific sales averages in this use case, rather than global cross-group metrics. 3. Window Frame Definition: The window frame clause defines the subset of rows within a partition used for each window calculation. For time series metrics like weekly moving averages, proper frame configuration ensures only the relevant chronological range of historical data is included in each calculation, which is a frequently tested competency to avoid metric calculation errors. References: PostgreSQL Documentation: Window Functions, https://www.postgresql.org/docs/current/tutorial-window.html Microsoft Learn: Calculate moving aggregates by using window functions in T-SQL

The scenario requires a managed near real-time streaming pipeline with minimal code for a simple transformation (capitalizing the serial number field) between Pub/Sub and BigQuery. The suggested answer C leverages Google Cloud's fully managed Dataflow service, which is purpose-built for streaming data processing. The pre-built Pub/Sub to BigQuery Dataflow template eliminates the need to write core pipeline code for ingestion, windowing, error handling, and BigQuery writing, while a small user-defined function (UDF) is only required to implement the simple serial number capitalization, meeting the minimal code requirement. This approach delivers low-latency near real-time processing, requires no infrastructure management, and aligns with all stated requirements for the use case. Option Analysis: A. Incorrect. While Pub/Sub to BigQuery subscriptions support direct writes, scheduling a transformation query every 5 minutes introduces batch latency that does not meet the near real-time requirement. This approach also adds overhead of managing scheduled queries and post-write transformation instead of processing data in-stream. B. Incorrect. This approach introduces unnecessary intermediate storage in Cloud Storage, which increases end-to-end latency and does not support true near real-time processing. It also requires writing and maintaining a full Cloud Run service for processing, which violates the minimal code requirement and adds operational overhead. C. Correct. This option uses a fully managed serverless streaming service (Dataflow) with a pre-built, production-ready template that handles all core pipeline functionality out of the box. The only code required is a small UDF to perform the simple string capitalization transformation, which meets the minimal code requirement. The pipeline processes data in-stream to deliver near real-time performance, and automatically handles scaling, error handling, and reliable writes to BigQuery. D. Incorrect. This option requires writing an entire Cloud Run service to handle message ingestion, transformation, BigQuery writes, message acknowledgement, batching, and error handling, which requires significantly more code than using a pre-built Dataflow template. It also requires manual configuration of scaling and reliability controls for the Cloud Run service, adding operational overhead that is not required with the managed Dataflow template approach. Key Concepts: 1. Dataflow Pre-built Templates: These are production-ready, open-source pipeline implementations for common data integration use cases such as Pub/Sub to BigQuery that eliminate the need for developers to write and maintain core pipeline code, reducing development effort and operational risk. 2. Dataflow Template User-Defined Functions (UDFs): Lightweight custom code snippets typically written in JavaScript or Python that can be added to pre-built Dataflow templates to implement simple, custom transformations without rewriting the entire pipeline, supporting minimal code requirements for custom business logic. 3. Near Real-Time Streaming Processing: Use cases requiring end-to-end latency of seconds to minutes require continuous in-stream processing rather than scheduled batch jobs, making streaming services like Dataflow the appropriate choice over batch or event-triggered batch approaches. References: Use the Pub/Sub to BigQuery template, https://cloud.google.com/dataflow/docs/guides/templates/provided/pubsub-to-bigquery User-defined functions for Dataflow templates

This question assesses the ability to select the optimal GCP data pipeline service that aligns with three core requirements: fast development of a scalable daily batch pipeline, support for data transformation during ingestion from Cloud Storage to BigQuery, and native visibility into data quality issues. The correct approach leverages a fully managed, low-code ETL tool that minimizes custom development effort while providing built-in data quality functionality. Cloud Data Fusion is purpose-built for this use case, as it reduces pipeline development time via pre-built connectors and transformation operators, auto-scales to handle variable daily sales file volumes, and includes native data quality features that eliminate the need for custom quality check implementation. Option Analysis: A. Correct. Cloud Data Fusion is a no-code/low-code fully managed ETL service that enables rapid development of batch pipelines. It natively supports Cloud Storage as a source for CSV files and BigQuery as a sink, includes a library of pre-built transformation operators to modify data during processing, and provides integrated data quality capabilities such as data profiling, validation rule configuration, and error record tracking to deliver insights into data quality issues. Its managed architecture automatically scales to meet daily batch load requirements, fulfilling all scenario requirements. B. Incorrect. Loading the CSV directly to BigQuery and using scheduled queries for transformations requires custom SQL development for all transformation and data quality checks, which increases development time and fails to meet the "quickly build" requirement. BigQuery scheduled queries also lack native, integrated data quality insight reporting, requiring additional custom implementation to surface data issues. C. Incorrect. This approach introduces an unnecessary redundant step of pre-loading the CSV file to BigQuery before running the Data Fusion pipeline, increasing processing overhead and complexity. Data Fusion can natively read directly from Cloud Storage, so this intermediate step provides no functional value for the given scenario. D. Incorrect. The Dataflow Cloud Storage to BigQuery batch template only supports basic loading functionality with limited out-of-the-box transformation and no native data quality capabilities. Building custom transformations and data quality checks in Dataflow requires coding expertise and significant development time, which does not meet the requirement to quickly build the pipeline. Key Concepts: 1. Cloud Data Fusion Capabilities: Cloud Data Fusion is a fully managed, low-code ETL/ELT service with pre-built connectors for GCP services including Cloud Storage and BigQuery, pre-built transformation operators, and native data quality tools, designed to reduce pipeline development time and operational overhead. 2. Batch Pipeline Workload Fit: Batch processing is designed for periodic, fixed-dataset workloads such as daily file ingestion, as it processes entire datasets at scheduled intervals and scales efficiently for large volume periodic loads. 3. Native Data Quality in Pipelines: Built-in data quality features in ETL tools eliminate the need for custom quality check development, reducing deployment time and providing consistent, standardized visibility into data errors and inconsistencies. References: Cloud Data Fusion Overview, https://cloud.google.com/data-fusion/docs/concepts/overview Use data quality in Cloud Data Fusion

The scenario requires automatic deletion of Cloud Storage objects 7 days after creation to reduce storage costs and keep the bucket organized, with minimal operational overhead. The suggested answer B leverages the native Cloud Storage lifecycle rule feature, which is a purpose-built, fully managed capability designed explicitly for this use case. For associate data practitioners, a core competency is selecting the most efficient, low-overhead native service to address common data operational tasks, rather than building custom solutions that add unnecessary complexity and cost. This approach requires no custom code, no management of additional services, and enforces the deletion rule consistently at the storage layer, ensuring compliance with the 7-day retention requirement without manual intervention. Option Analysis: A. Incorrect. This solution requires provisioning and managing two separate GCP services (Cloud Scheduler and Cloud Run) plus writing, testing, and maintaining custom code to enumerate and delete old objects. It introduces unnecessary operational overhead, and running weekly means objects may remain in storage for up to 14 days, failing to consistently enforce the 7-day retention requirement. It is far less efficient than the native lifecycle rule feature. B. Correct. Cloud Storage lifecycle rules are a fully managed, native feature built directly into the Cloud Storage service to automate object management actions. Configuring a rule to delete objects older than 7 days requires no custom code, no additional service management, and runs automatically to delete objects promptly once they meet the age threshold. It perfectly aligns with the requirement to reduce cost and organize the bucket with minimal effort, which is a standard best practice for data practitioners working with cloud object storage. C. Incorrect. Dataflow is a managed service designed for large-scale batch and stream data processing workloads such as ETL, data transformation, and analytics processing. Using Dataflow for simple object deletion is a significant misalignment of service purpose, introducing excessive cost, operational complexity, and resource overhead for a task that is handled natively by Cloud Storage at no additional cost. D. Incorrect. While running daily is more frequent than the weekly cadence in option A, this solution still requires writing and maintaining custom Cloud Run function code, handling edge cases such as large volumes of objects that may cause function timeouts, and managing invocation permissions. It introduces avoidable operational risk and overhead compared to the native, no-code lifecycle rule feature. Key Concepts: 1. Cloud Storage Lifecycle Management: This is a core native Cloud Storage capability that allows users to define automated actions for objects based on predefined conditions such as object age, version count, or storage class duration. It eliminates the need for custom operational scripts for standard object management tasks. 2. Cloud Cost and Operational Efficiency for Data Practitioners: Associate data practitioners are expected to prioritize purpose-built, low-overhead native cloud services over custom developed solutions for common use cases, to reduce operational burden, minimize error risk, and optimize storage and compute costs. 3. Service Use Case Alignment: A core certification competency is matching workload requirements to the appropriate GCP service, avoiding overprovisioning of general-purpose compute or processing services for tasks that are handled by specialized, lower-cost native features. References: Object Lifecycle Management | Cloud Storage, Google Cloud, https://cloud.google.com/storage/docs/lifecycle Google Cloud Associate Data Practitioner Exam Guide